#！/bin/bash

username=csp_prod
password=csp_prod
host=192.168.1.194
sid=orcl

today=`date -d today '+%Y%m%d'`

PRG="$0"

while [ -h "$PRG" ] ; do
  ls=`ls -ld "$PRG"`
  link=`expr "$ls" : '.*-> \(.*\)$'`
  if expr "$link" : '/.*' > /dev/null; then
    PRG="$link"
  else
    PRG=`dirname "$PRG"`/"$link"
  fi
done

PRGDIR=`dirname "$PRG"`
PRG_HOME=`cd "$PRGDIR" >/dev/null; pwd`

path=$PRG_HOME/sqls/$username-ddl-procedure-$today.sql

echo "Starting export $username 's procedure and function ddl sqls to $path ..."

sqlplus -S $username/$password@$host/$sid <<EOF >/dev/null 2>&1
set verify off;
set wrap off;
set echo off;
set term off;
set heading off;
set feedback off;
set pagesize 0;
set line 3000;
set linesize 10000;
set long 90000;
set trimspool on;
col a for a200 wrapped word
EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'PRETTY',true);
EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'SQLTERMINATOR',true);
spool $path
select dbms_metadata.get_ddl(t1.OBJECT_TYPE,t1.object_name) a from USER_OBJECTS t1 where t1.OBJECT_TYPE in ('FUNCTION','PROCEDURE') order by t1.OBJECT_TYPE, t1.OBJECT_NAME;
spool off
quit
EOF

echo "Finished export $username 's procedure and function ddl sqls to $path !"

